/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import utils.JAXBUtil;
import xmlobject.DTO.OrderCheckout.Products;
import xmlobject.DTO.OrderItems.OrderItem;
import xmlobject.DTO.OrderItems.OrderItems;
import xmlobject.DTO.Orders.Order;
import xmlobject.DTO.Orders.Orders;
import xmlobject.DTO.Products.Product;

/**
 *
 * @author minhsong
 */
public class OrdersDAL extends Dataconection {

    private Order ReadOrderFromDS(ResultSet rs) {
        Order result = new Order();
        if (rs != null) {
            try {
                result.setOrderID(rs.getLong("OrderID"));
                result.setStoreID(rs.getLong("StoreID"));
                result.setReciverName(rs.getString("ReciverName"));
                result.setPhoneNumber(rs.getString("PhoneNumber"));
                result.setAddress1(rs.getString("Address1"));
                result.setAddress2(rs.getString("Address2"));
                result.setCreateBy(rs.getString("CreateBy"));
                result.setCreateDate(rs.getDate("CreateDate"));
                result.setUpdateBy(rs.getString("UpdateBy"));
                result.setUpdateDate(rs.getDate("UpdateDate"));
                result.setCustomer(rs.getString("Customer"));
            } catch (SQLException ex) {
                Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }

    public Order getOrderById(int id) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call sporderGet(?) }");
            proc_stmt.setLong("OrderID", id);
            rs = proc_stmt.executeQuery();
            if (rs.next()) {
                return ReadOrderFromDS(rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Orders getAllOrder() {
        Orders result = new Orders();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call sporderGetList() }");
            rs = proc_stmt.executeQuery();
            while (rs.next()) {
                result.getOrder().add(ReadOrderFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public Orders getAllOrdersByCustomer(String customer) {
        Orders result = new Orders();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call sporderSelectAllByCustomer(?) }");
            proc_stmt.setString("Customer", customer);
            rs = proc_stmt.executeQuery();
            while (rs.next()) {
                result.getOrder().add(ReadOrderFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public Order Create(Order value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call sporderInsert(?,?,?,?,?,?,?,?,?) }");
            proc_stmt.registerOutParameter("OrderID", java.sql.Types.INTEGER);
            proc_stmt.setLong("StoreID", 1);
            proc_stmt.setString("ReciverName", value.getReciverName());
            proc_stmt.setString("PhoneNumber", value.getPhoneNumber());
            proc_stmt.setString("Address1", value.getAddress1());
            proc_stmt.setString("Address2", value.getAddress2());
            proc_stmt.setString("CreateBy", value.getCreateBy());
            proc_stmt.setString("UpdateBy", value.getUpdateBy());
            proc_stmt.setString("Customer", value.getCustomer());
            proc_stmt.execute();
            value.setOrderID(proc_stmt.getLong("OrderID"));
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return value;
    }

    public Order Edit(Order value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call sporderInsert(?,?,?,?,?,?,?,?) }");
            proc_stmt.setLong("OrderID", value.getOrderID());
            proc_stmt.setLong("StoreID", value.getStoreID());
            proc_stmt.setString("ReciverName", value.getReciverName());
            proc_stmt.setString("PhoneNumber", value.getPhoneNumber());
            proc_stmt.setString("Address1", value.getAddress1());
            proc_stmt.setString("Address2", value.getAddress2());
            proc_stmt.setString("UpdateBy", value.getUpdateBy());
            proc_stmt.setString("Customer", value.getCustomer());
            proc_stmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return value;
    }

    public boolean Delete(int value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call sporderDelete(?) }");
            proc_stmt.setLong("OrderID", value);
            return proc_stmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean DeleteAllOrderByCustomer(String cutomers) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call sporderDeleteAllByCustomer(?) }");
            proc_stmt.setString("Customer", cutomers);
            return proc_stmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public int addNewOrder(Products products, Order order) {
        try {
            int orderID = -1;
            ProductDAL proService = new ProductDAL();
            OrdersDAL orderService = new OrdersDAL();
            OrderItemDAL orderItemService = new OrderItemDAL();
            DAOConnection.setAutoCommit(false);
            boolean isValid = true;
            Order newOrder = orderService.Create(order);
            orderID=(int)newOrder.getOrderID();
            for (int i = 0; i < products.getProduct().size(); i++) {
                //pre_stmt = DAOConnection.prepareStatement("update Stock=Stock-? from tbl_product where ProductID=? and Stock>=? ");
                Product pro = proService.getProductById(products.getProduct().get(i).getProductID().intValue());
                if (pro.getStock() >= products.getProduct().get(i).getProductQty().intValue()) {
                    pro.setStock(pro.getStock() - products.getProduct().get(i).getProductQty().intValue());
                    proService.Edit(pro);
                    OrderItem orderItem = new OrderItem();
                    orderItem.setOrderID(newOrder.getOrderID());
                    orderItem.setPrice(pro.getPrice());
                    orderItem.setQuantity(products.getProduct().get(i).getProductQty().intValue());
                    orderItem.setProductID(products.getProduct().get(i).getProductID().intValue());
                    orderItemService.Create(orderItem);
                } else {
                    isValid = false;
                    break;
                }
            }
            if (isValid) {
                DAOConnection.commit();
            } else {
                DAOConnection.close();
                return -1;
            }
            if (isValid) {
                return orderID;
            }
            return -1;
        } catch (SQLException ex) {
            try {
                DAOConnection.rollback();
                DAOConnection.close();
                Logger.getLogger(OrdersDAL.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex1) {
                Logger.getLogger(OrdersDAL.class.getName()).log(Level.SEVERE, null, ex1);
            }
        }
        return -1;
    }

    public String getPFD(int OrderID) {
        OrderItemDAL OrderItemService = new OrderItemDAL();
        ProductDAL productDAL = new ProductDAL();
        Order order = getOrderById(OrderID);
        OrderItems listItem = OrderItemService.getAllOrderItemByOrderId(OrderID);
        String stringPDF = "<Products>";
        for (int i = 0; i < listItem.getOrderItem().size(); i++) {
            stringPDF += "<Product>";
            stringPDF += "<ProductID>" + listItem.getOrderItem().get(i).getItemID() + "</ProductID>";
            stringPDF += "<ProductName>" + productDAL.getProductById((int) listItem.getOrderItem().get(i).getProductID()).getProductName() + "</ProductName>";
            stringPDF += "<ProductPrice>" + listItem.getOrderItem().get(i).getPrice() + "</ProductPrice>";
            stringPDF += "<ProductQty>" + listItem.getOrderItem().get(i).getQuantity() + "</ProductQty>";
            stringPDF += "<Subtotal>" + listItem.getOrderItem().get(i).getQuantity() * listItem.getOrderItem().get(i).getPrice() + "</Subtotal>";
            stringPDF += "<ProductImage>" + productDAL.getProductById((int) listItem.getOrderItem().get(i).getProductID()).getProductImage() + "</ProductImage>";
            stringPDF += "</Product>";
        }
        stringPDF += "</Products>";
        return stringPDF;
    }
}
